﻿using System;
using System.Collections.Generic;
using System.Text;
using OgilvyOne.IDAL;
using System.Data;
using System.Data.SqlClient;
using OgilvyOne.Model;

namespace OgilvyOne.SqlServerDAL
{
    public class User : IUser
    {
        string connstr = SQLHelper.CONN_STRING_NON_DTC;
        #region IUser 成员

        public bool Exists(string name, int aid)
        {
            string SQL_SELECTByName = "select * from [CMS_USERS] where USERNAME=@USERNAME";
            if (aid>0)
            {
                SQL_SELECTByName += " and AID not in ("+aid+")"; 
            }
            SqlParameter[] param = {
                                       new SqlParameter("@USERNAME", name)                                      
                                    };           
            return int.Parse(SQLHelper.ExecuteScalar(connstr, CommandType.Text, SQL_SELECTByName, param).ToString()) > 0;

        }

        public int Add(OgilvyOne.Model.UsersModel model)
        {
            string SQL_INSERT_USER = @"INSERT INTO [CMS_USERS] ([USERNAME],[USERPASS],[USERROLE],realname,email,deportment,mobile,[ADDTIME]) VALUES (@USERNAME,@USERPASS,@USERROLE,@realname,@email,@deportment,@mobile,getDate())";
            SqlParameter[] parameters = {
					    new SqlParameter("@USERNAME", SqlDbType.VarChar,50),
					    new SqlParameter("@USERPASS", SqlDbType.VarChar,50),
					    new SqlParameter("@USERROLE", SqlDbType.VarChar,200),
					    new SqlParameter("@realname", SqlDbType.VarChar,50),
					    new SqlParameter("@email", SqlDbType.VarChar,100),
					    new SqlParameter("@deportment", SqlDbType.VarChar,100),
					    new SqlParameter("@mobile", SqlDbType.VarChar,50)};
            if (model.Username != null)
            {
                parameters[0].Value = model.Username;
            }
            else
            {
                parameters[0].Value = System.DBNull.Value;
            }
            if (model.Userpass != null)
            {
                parameters[1].Value = model.Userpass;
            }
            else
            {
                parameters[1].Value = System.DBNull.Value;
            }
            if (model.Userrole != null)
            {
                parameters[2].Value = model.Userrole;
            }
            else
            {
                parameters[2].Value = System.DBNull.Value;
            }
            if (model.Realname != null)
            {
                parameters[3].Value = model.Realname;
            }
            else
            {
                parameters[3].Value = System.DBNull.Value;
            }
            if (model.Email != null)
            {
                parameters[4].Value = model.Email;
            }
            else
            {
                parameters[4].Value = System.DBNull.Value;
            }
            if (model.Deportment != null)
            {
                parameters[5].Value = model.Deportment;
            }
            else
            {
                parameters[5].Value = System.DBNull.Value;
            }
            if (model.Mobile != null)
            {
                parameters[6].Value = model.Mobile;
            }
            else
            {
                parameters[6].Value = System.DBNull.Value;
            }

            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, SQL_INSERT_USER, parameters);
        }

        public bool Update(OgilvyOne.Model.UsersModel model)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("update CMS_USERS set ");
            strSql.Append("USERNAME=@USERNAME,");
            strSql.Append("USERPASS=@USERPASS,");
            strSql.Append("USERROLE=@USERROLE,");
            strSql.Append("ADDTIME=@ADDTIME");
            strSql.Append(" where AID=@AID");
            SqlParameter[] parameters = {
					new SqlParameter("@USERNAME", SqlDbType.VarChar,50),
					new SqlParameter("@USERPASS", SqlDbType.VarChar,50),
					new SqlParameter("@USERROLE", SqlDbType.VarChar,200),
					new SqlParameter("@ADDTIME", SqlDbType.DateTime),
					new SqlParameter("@AID", SqlDbType.Int,4)};
            #region
            //if (model.Username != null)
            //{
            //    parameters[0].Value = model.Username;
            //}
            //else
            //{
            //    parameters[0].Value = System.DBNull.Value;
            //}
            //if (model.Userpass != null)
            //{
            //    parameters[1].Value = model.Userpass;
            //}
            //else
            //{
            //    parameters[1].Value = System.DBNull.Value;
            //}
            //if (model.Userrole != null)
            //{
            //    parameters[2].Value = model.Userrole;
            //}
            //else
            //{
            //    parameters[2].Value = System.DBNull.Value;
            //}
            //if (model.Realname != null)
            //{
            //    parameters[3].Value = model.Realname;
            //}
            //else
            //{
            //    parameters[3].Value = System.DBNull.Value;
            //}
            //if (model.Email != null)
            //{
            //    parameters[4].Value = model.Email;
            //}
            //else
            //{
            //    parameters[4].Value = "";
            //}
            //if (model.Deportment != null)
            //{
            //    parameters[5].Value = model.Deportment;
            //}
            //else
            //{
            //    parameters[5].Value = "";
            //}
            //if (model.Mobile != null)
            //{
            //    parameters[6].Value = model.Mobile;
            //}
            //else
            //{
            //    parameters[6].Value = "";
            //}
            //if (model.Addtime != null)
            //{
            //    parameters[7].Value = model.Addtime;
            //}
            //else
            //{
            //    parameters[7].Value = DateTime.Now;
            //}
            //if (model.Aid.ToString() != null)
            //{
            //    parameters[8].Value = model.Aid;
            //}
            //else
            //{
            //    parameters[8].Value = 0;
            //}            
            #endregion
            parameters[0].Value = model.Username;
            parameters[1].Value = model.Userpass;
            if (model.Userrole != null)
            {
                parameters[2].Value = model.Userrole;
            }
            else
            {
                parameters[2].Value = DBNull.Value;
            }
            if (model.Addtime != null)
            {
                parameters[3].Value = model.Addtime;
            }
            else
            {
                parameters[3].Value = DateTime.Now;
            }
            parameters[4].Value = model.Aid;
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), parameters) > 0;

        }

        public bool Delete(int AID)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from CMS_USERS ");
            strSql.Append(" where AID=@AID");
            SqlParameter[] parameters = {
					new SqlParameter("@AID", SqlDbType.Int,4)
             };
            parameters[0].Value = AID;
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), parameters) > 0;
        }

        public bool DeleteList(string str)
        {

            StringBuilder strSql = new StringBuilder();
            strSql.Append("delete from CMS_USERS ");
            strSql.Append(" where AID in (" + str + ")  ");
            return SQLHelper.ExecuteNonQuery(connstr, CommandType.Text, strSql.ToString(), null) > 0;
        }

        public OgilvyOne.Model.UsersModel GetModel(int AID)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 AID,USERNAME,USERPASS,USERROLE,realname,email,deportment,mobile,ADDTIME from CMS_USERS ");
            strSql.Append(" where AID=@AID");
            SqlParameter[] parameters = {
					new SqlParameter("@AID", SqlDbType.Int,4)
            };
            parameters[0].Value = AID;

            OgilvyOne.Model.UsersModel model = new OgilvyOne.Model.UsersModel();
            using (SqlDataReader dr = SQLHelper.ExecuteReader(connstr, CommandType.Text, strSql.ToString(), parameters))
            {
                while (dr.Read())
                {
                    if (!dr.IsDBNull(0))
                    {
                        model.Aid = dr.GetInt32(0);
                    }
                    if (!dr.IsDBNull(1))
                    {
                        model.Username = dr.GetString(1);
                    }
                    if (!dr.IsDBNull(2))
                    {
                        model.Userpass = dr.GetString(2);
                    }
                    if (!dr.IsDBNull(3))
                    {
                        model.Userrole = dr.GetString(3);
                    }
                    if (!dr.IsDBNull(4))
                    {
                        model.Realname = dr.GetString(4);
                    }
                    if (!dr.IsDBNull(5))
                    {
                        model.Email = dr.GetString(5);
                    }
                    if (!dr.IsDBNull(6))
                    {
                        model.Deportment = dr.GetString(6);
                    }
                    if (!dr.IsDBNull(7))
                    {
                        model.Mobile = dr.GetString(7);
                    }
                    if (!dr.IsDBNull(8))
                    {
                        model.Addtime = dr.GetDateTime(8);
                    }

                }
            }
            return model;
        }

        public System.Data.DataSet GetList(string strWhere)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select AID,USERNAME,USERPASS,USERROLE,realname,email,deportment,mobile,ADDTIME ");
            strSql.Append(" FROM CMS_USERS ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            return SQLHelper.Query(strSql.ToString());
        }

        /// <summary>
        /// 获得前几行数据
        /// </summary>
        /// <param name="Top"> 前几行</param>
        /// <param name="strWhere">sql条件</param>
        /// <param name="filedOrder">排序方式order by</param>
        /// <returns></returns>
        public DataSet GetList(int Top, string strWhere, string filedOrder)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select ");
            if (Top > 0)
            {
                strSql.Append(" top " + Top.ToString());
            }
            strSql.Append(" AID,USERNAME,USERPASS,USERROLE,realname,email,deportment,mobile,ADDTIME ");
            strSql.Append(" FROM CMS_USERS ");
            if (strWhere.Trim() != "")
            {
                strSql.Append(" where " + strWhere);
            }
            strSql.Append(" order by " + filedOrder);
            return SQLHelper.Query(strSql.ToString());
        }

        public UsersModel GetModel(string userName)
        {
            StringBuilder strSql = new StringBuilder();
            strSql.Append("select  top 1 AID,USERNAME,USERPASS,USERROLE,realname,email,deportment,mobile,ADDTIME from CMS_USERS ");
            strSql.Append(" where USERNAME=@USERNAME");
            SqlParameter[] parameters = {
					new SqlParameter("@USERNAME", SqlDbType.VarChar,50)
            };
            parameters[0].Value = userName;

            OgilvyOne.Model.UsersModel model = new OgilvyOne.Model.UsersModel();
            using (SqlDataReader dr = SQLHelper.ExecuteReader(connstr, CommandType.Text, strSql.ToString(), parameters))
            {
                while (dr.Read())
                {
                    if (!dr.IsDBNull(0))
                    {
                        model.Aid = dr.GetInt32(0);
                    }
                    if (!dr.IsDBNull(1))
                    {
                        model.Username = dr.GetString(1);
                    }
                    if (!dr.IsDBNull(2))
                    {
                        model.Userpass = dr.GetString(2);
                    }
                    if (!dr.IsDBNull(3))
                    {
                        model.Userrole = dr.GetString(3);
                    }
                    if (!dr.IsDBNull(4))
                    {
                        model.Realname = dr.GetString(4);
                    }
                    if (!dr.IsDBNull(5))
                    {
                        model.Email = dr.GetString(5);
                    }
                    if (!dr.IsDBNull(6))
                    {
                        model.Deportment = dr.GetString(6);
                    }
                    if (!dr.IsDBNull(7))
                    {
                        model.Mobile = dr.GetString(7);
                    }
                    if (!dr.IsDBNull(8))
                    {
                        model.Addtime = dr.GetDateTime(8);
                    }

                }
            }
            return model;
        }

        #endregion
    }
}
